package com.ruoyi.admin.mapper;

import java.time.LocalDateTime;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.ruoyi.admin.domain.NameDataPanel;
import com.ruoyi.admin.domain.DailyData;
import java.util.List;
import java.math.BigDecimal;

/**
 * 仪表盘数据访问接口
 * 提供数据看板所需的统计数据查询功能
 * 
 * @author ouran
 * @date 2025-06-16
 */
public interface NameDataPanelMapper {
    /**
     * 查询仪表盘统计数据
     * 
     * @param today            今日起始时间
     * @param tomorrow         明日起始时间
     * @param yesterday        昨日起始时间
     * @param firstDayOfMonth  本月第一天
     * @param firstDayNextMonth 下月第一天
     * @param firstDayLastMonth 上月第一天
     * @param firstDayOfYear   本年第一天
     * @param firstDayNextYear 下年第一天
     * @return 仪表盘统计数据
     */
    @Select("SELECT " +
            "    SUM(CASE WHEN o.pay_time >= #{today} AND o.pay_time < #{tomorrow} THEN o.total_price * 1.0 ELSE 0 END) AS todayEarnings, " +
            "    SUM(CASE WHEN o.pay_time >= #{today} AND o.pay_time < #{tomorrow} THEN 1 ELSE 0 END) AS todayOrders, " +
            "    SUM(CASE WHEN o.pay_time >= #{today} AND o.pay_time < #{tomorrow} THEN o.total_price ELSE 0 END) AS todayGmv, " +
            "    " +
            "    SUM(CASE WHEN o.pay_time >= #{yesterday} AND o.pay_time < #{today} THEN o.total_price * 1.0 ELSE 0 END) AS yesterdayEarnings, " +
            "    SUM(CASE WHEN o.pay_time >= #{yesterday} AND o.pay_time < #{today} THEN 1 ELSE 0 END) AS yesterdayOrders, " +
            "    SUM(CASE WHEN o.pay_time >= #{yesterday} AND o.pay_time < #{today} THEN o.total_price ELSE 0 END) AS yesterdayGmv, " +
            "    " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayOfMonth} AND o.pay_time < #{firstDayNextMonth} THEN o.total_price * 1.0 ELSE 0 END) AS monthEarnings, " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayOfMonth} AND o.pay_time < #{firstDayNextMonth} THEN 1 ELSE 0 END) AS monthOrders, " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayOfMonth} AND o.pay_time < #{firstDayNextMonth} THEN o.total_price ELSE 0 END) AS monthGmv, " +
            "    " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayLastMonth} AND o.pay_time < #{firstDayOfMonth} THEN o.total_price * 1.0 ELSE 0 END) AS lastMonthEarnings, " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayLastMonth} AND o.pay_time < #{firstDayOfMonth} THEN 1 ELSE 0 END) AS lastMonthOrders, " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayLastMonth} AND o.pay_time < #{firstDayOfMonth} THEN o.total_price ELSE 0 END) AS lastMonthGmv, " +
            "    " +
            "    SUM(CASE WHEN o.pay_time >= #{firstDayOfYear} AND o.pay_time < #{firstDayNextYear} THEN o.total_price * 1.0 ELSE 0 END) AS yearEarnings " +
            "FROM alipay_name_order o " +
            "WHERE o.status = 2 AND o.pay_time IS NOT NULL")
    NameDataPanel selectDashboardStats(
        @Param("today") LocalDateTime today,
        @Param("tomorrow") LocalDateTime tomorrow,
        @Param("yesterday") LocalDateTime yesterday,
        @Param("firstDayOfMonth") LocalDateTime firstDayOfMonth,
        @Param("firstDayNextMonth") LocalDateTime firstDayNextMonth,
        @Param("firstDayLastMonth") LocalDateTime firstDayLastMonth,
        @Param("firstDayOfYear") LocalDateTime firstDayOfYear,
        @Param("firstDayNextYear") LocalDateTime firstDayNextYear
    );

    /**
     * 查询近30天的每日订单和收益数据
     * 
     * @param thirtyDaysAgo 30天前的日期
     * @return 近30天的每日数据列表
     */
    @Select("SELECT "+ 
            "    DATE(o.pay_time) AS date, " +
            "    SUM(1) AS orders, " +
            "    SUM(o.total_price * 1.0) AS earnings, " +
            "    SUM(o.total_price) AS gmv " +
            "FROM alipay_name_order o " +
            "WHERE o.status = 2 AND o.pay_time >= #{thirtyDaysAgo} " +
            "GROUP BY DATE(o.pay_time) " +
            "ORDER BY DATE(o.pay_time) ASC")
    List<DailyData> selectLastThirtyDaysData(@Param("thirtyDaysAgo") LocalDateTime thirtyDaysAgo);

    /**
     * 查询指定年份和月份的收益
     * 
     * @param userId 用户ID
     * @param year 年份
     * @param month 月份
     * @return 收益金额
     */
    @Select("SELECT SUM(o.total_price * 1.0) FROM alipay_name_order o " +
            "WHERE o.status = 2 AND o.pay_time IS NOT NULL " +
            "AND YEAR(o.pay_time) = #{year} AND MONTH(o.pay_time) = #{month} " +
            "AND o.user_id = #{userId}")
    BigDecimal selectMonthEarnings(@Param("userId") String userId, @Param("year") int year, @Param("month") int month);
    
    /**
     * 查询指定年份的收益
     * 
     * @param userId 用户ID
     * @param year 年份
     * @return 收益金额
     */
    @Select("SELECT SUM(o.total_price * 1.0) FROM alipay_name_order o " +
            "WHERE o.status = 2 AND o.pay_time IS NOT NULL " +
            "AND YEAR(o.pay_time) = #{year} " +
            "AND o.user_id = #{userId}")
    BigDecimal selectYearEarnings(@Param("userId") String userId, @Param("year") int year);
    
    /**
     * 查询已提现的总金额
     * 
     * @param userId 用户ID
     * @return 已提现金额
     */
    @Select("SELECT SUM(amount) FROM alipay_withdrawal_record " +
            "WHERE status = 1 AND user_id = #{userId}")
    BigDecimal selectWithdrawnAmount(@Param("userId") String userId);
    
    /**
     * 查询指定时间段内的总收益
     * 
     * @param userId 用户ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 总收益
     */
    @Select("SELECT SUM(o.total_price * 1.0) FROM alipay_name_order o " +
            "WHERE o.status = 2 AND o.pay_time IS NOT NULL " +
            "AND o.pay_time >= #{startTime} AND o.pay_time < #{endTime} " +
            "AND o.user_id = #{userId}")
    BigDecimal selectEarningsBetween(@Param("userId") String userId, 
                                    @Param("startTime") LocalDateTime startTime, 
                                    @Param("endTime") LocalDateTime endTime);
}